package com.isyscore.os.metadata.utils;

import com.isyscore.os.metadata.database.AbstractDatabase;
import com.isyscore.os.metadata.database.MysqlDatabase;
import com.isyscore.os.metadata.enums.KettleJobIncreFieldType;
import lombok.extern.log4j.Log4j2;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.util.TablesNamesFinder;

import java.util.*;

@Log4j2
public class IncrSqlBuilder extends TablesNamesFinder {
    private String sql;
    private String incrTable;
    private String incrField;
    private String origIncrField;
    private String incStartValue;
    private String incEndValue;
    private boolean first;
    private AbstractDatabase db;
    private String incrFieldType;

    private List<String> tables;
    private List<String> otherItemNames;

    private List<Map> change_tables = new ArrayList<Map>();
    private String table_now;
    private String alias;
    private String fieldType;


    @Override
    public void visit(Table tableName) {

        String tableWholeName = tableName.getFullyQualifiedName();
        alias=tableName.getAlias()==null?tableName.toString():tableName.getAlias().toString();
        table_now = tableWholeName;
        if (!this.otherItemNames.contains(tableWholeName.toLowerCase()) && !this.tables.contains(tableWholeName)) {
            this.tables.add(tableWholeName);
        }

        Map m = new HashMap<>();
        m.put(incrTable,transTable(incrTable));
        change_tables.add(m);
    }

    @Override
    public List<String> getTableList(Statement statement) {
        this.init();
        statement.accept(this);
        return this.tables;
    }

    protected void init() {
        this.otherItemNames = new ArrayList();
        this.tables = new ArrayList();
//        this.plainSelect = new PlainSelect();
    }

    @Override
    public void visit(PlainSelect plainSelect) {

        Iterator var2;
        if (plainSelect.getSelectItems() != null) {
            var2 = plainSelect.getSelectItems().iterator();
            while(var2.hasNext()) {
                SelectItem item = (SelectItem)var2.next();
                item.accept(this);
            }
        }

        if (plainSelect.getFromItem() != null) {
            plainSelect.getFromItem().accept(this);

            Table table = new Table((String) change_tables.get(0).get(table_now));
            if(plainSelect.getJoins()!=null){
                table.setAlias(new Alias(alias,false));
            }else{
                table.setAlias(new Alias("TEMP",false));
            }
            plainSelect.setFromItem(table);

        }

        if (plainSelect.getJoins() != null) {
            var2 = plainSelect.getJoins().iterator();

            while(var2.hasNext()) {
                Join join = (Join)var2.next();
                String alias = join.getRightItem().getAlias().toString();
                String table_tmp=join.getRightItem().toString();
                table_now = table_tmp.substring(0,table_tmp.indexOf(" "));
                Table joinTable = new Table();
                joinTable.setName((String) change_tables.get(0).get(table_now));
                joinTable.setAlias(new Alias(alias,false));
                join.setRightItem(joinTable);
                join.getRightItem().accept(this);
            }
        }

        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(this);
        }

        if (plainSelect.getOracleHierarchical() != null) {
            plainSelect.getOracleHierarchical().accept(this);
        }
    }

    public String transTable(String table) {
//        String cleanTable = db.withOutEscapeTableName(table);
//        if (cleanTable.equals(incrTable)) {
            if(KettleJobIncreFieldType.valueOf(incrFieldType)==KettleJobIncreFieldType.time && db.isLegalDateType(fieldType)){
                incStartValue = db.getParseStr2DateEl("'"+incStartValue+"'");
                incEndValue = db.getParseStr2DateEl("'"+incEndValue+"'");
            } else if (KettleJobIncreFieldType.valueOf(incrFieldType)==KettleJobIncreFieldType.time && !db.isLegalDateType(fieldType)) {
                incStartValue = "'"+incStartValue+"'";
                incEndValue = "'"+incEndValue+"'";
            }

        if (alias.equals(db.withOutEscapeColName(table))) {
                if (first) {
                    table = "(select * from " + table + "  where " + incrField + ">=" + incStartValue + " and " + incrField + "<=" + incEndValue + ") ";
                } else {
                    table = "(select * from " + table + "  where " + incrField + ">" + incStartValue + " and " + incrField + "<=" + incEndValue + ") " ;
                }
            } else {
                if (first) {
                    table = "(select * from " + table + "  where " + incrField + ">=" + incStartValue + " and " + incrField + "<=" + incEndValue + ")";
                } else {
                    table = "(select * from " + table + "  where " + incrField + ">" + incStartValue + " and " + incrField + "<=" + incEndValue + ")";
                }
            }
//        }

        return table;
    }

    public String generateIncrSql(String sql, String incrTable, String incrFieldType, String incrField,String origIncrField, String incStartValue, String incEndValue, boolean first, AbstractDatabase db, String fieldType){
        this.sql = sql;
        this.incrTable = incrTable;
        this.incrField = incrField;
        this.origIncrField = origIncrField;
        this.incStartValue = incStartValue;
        this.incEndValue = incEndValue;
        this.first = first;
        this.db = db;
        this.fieldType = fieldType;
        this.incrFieldType = incrFieldType;
        Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
        getTableList(statement);
        log.info("增量SQL："+ statement);
        return statement.toString();
    }

    public static void main(String[] args) throws JSQLParserException {
        IncrSqlBuilder t = new IncrSqlBuilder();
        String build = t.generateIncrSql("SELECT \"ID\", \"NAME\", \"D\", \"TM\" FROM SCOTT.\"JFLD\"   ", "SCOTT.\"JFLD\"", "number", "\"id\"", "\"id\"","10", "100", false, new MysqlDatabase(), null);
//        List<SQLStatement> stmtList = SQLUtils.parseStatements("select * from (select * from m) m left join m_1  on m_1.id = m.id", dbType);
        System.out.println(build);
    }

}

